package com.msg.actions;

import java.io.File;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.tomcat.util.http.fileupload.FileItem;
import org.apache.tomcat.util.http.fileupload.FileUploadException;
import org.apache.tomcat.util.http.fileupload.disk.DiskFileItemFactory;
import org.apache.tomcat.util.http.fileupload.servlet.ServletFileUpload;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

import com.msg.beans.EventBean;
import com.msg.beans.ForecastBean;
import com.msg.beans.GraphBean;
import com.msg.beans.PriceBean;
import com.msg.beans.SeasonBean;
import com.msg.beans.SeasonForecastBean;
import com.msg.beans.TicketBean;
import com.msg.beans.TicketGroupBean;
import com.msg.dao.DBConnectionFactory;
import com.msg.utils.ProjectConstants;
import com.msg.xml.XMLToDB;

public class ActionHelper {

	public ActionHelper() {
		// TODO Auto-generated constructor stub
	}

	/*
	 * Public helper method for Bookmanager to search books.
	 * 
	 * @author Rijuta.
	 */
	public String getEventData() {
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		EventBean eventBean = null;
		JSONObject json = new JSONObject();
		JSONArray events = new JSONArray();

		try {
			pstmt = conn.prepareStatement("select * from event");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				eventBean = new EventBean();
				eventBean.setEvent_Id(rs.getInt(1));
				eventBean.setOpponents_Id(rs.getInt(2));
				eventBean.setEvent_Code(rs.getString(3));
				eventBean.setSeason(rs.getInt(4));
				events.put(eventBean.toJSONObject());
			}
			json.put("items", events);
			rs.close();
		} catch (SQLException | JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return json.toString();
	}

	public String getTicketData() {
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		TicketBean ticketBean = null;
		JSONObject json = new JSONObject();
		JSONArray tickets = new JSONArray();

		try {
			/*
			 * pstmt = conn.prepareStatement(
			 * "SELECT evt.Event_Code, tl.Ticket_Level_Id, pc.Price, pc.Sold_Seats, pc.Total_Amount_Sold "
			 * +
			 * " FROM nyliberty.price_code pc, nyliberty.ticket_level tl, nyliberty.event evt "
			 * + " where pc.Event_Id = evt. Event_Id " +
			 * " and tl.Ticket_Level_Id = pc.Ticket_Level_Id;");
			 */
			pstmt = conn
					.prepareStatement("SELECT evt.Event_Code,tt.ticket_type_code,pl.Price_Level_Code, tl.Ticket_Level_Name, "
							+ " pc.Price, pc.Sold_Seats, pc.Total_Amount_Sold  "
							+ " FROM nyliberty.price_code pc, "
							+ " nyliberty.ticket_level tl, "
							+ " nyliberty.event evt, "
							+ " nyliberty.ticket_group tg, "
							+ " ticket_type tt, "
							+ " price_level_index pl "
							+ " where pc.Event_Id = evt. Event_Id "
							+ " and tl.Ticket_Level_Id = pc.Ticket_Level_Id "
							+ " and tg.Ticket_Group_Id=pc.Ticket_Group_Id "
							+ " and tt.ticket_type_id=tg.ticket_type_id "
							+ " and pl.price_level_id=tg.price_level_id");

			rs = pstmt.executeQuery();
			while (rs.next()) {

				ticketBean = new TicketBean();
				ticketBean.setEvent_Code(rs.getString(1));
				ticketBean.setTicket_type_code(rs.getString(2));
				ticketBean.setPrice_Level_Code(rs.getString(3));
				ticketBean.setTicket_Level_Name(rs.getString(4));
				ticketBean.setPrice(rs.getInt(5));
				ticketBean.setSold_Seats(rs.getInt(6));
				ticketBean.setTotal_Amount(rs.getInt(7));

				tickets.put(ticketBean.toJSONObject());
			}
			json.put("items", tickets);
			rs.close();
		} catch (SQLException | JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return json.toString();
	}

	private Connection getconnection()

	{
		DBConnectionFactory confactory = new DBConnectionFactory();
		Connection conn = confactory.getConnection();
		return conn;
	}

	public void uploadXml(HttpServletRequest request) {
		DiskFileItemFactory fileItemFactory = new DiskFileItemFactory();

		ServletFileUpload uploadHandler = new ServletFileUpload(fileItemFactory);
		try {
			List items = uploadHandler.parseRequest(request);
			Iterator itr = items.iterator();
			while (itr.hasNext()) {
				FileItem item = (FileItem) itr.next();

				if (item.isFormField()) {
					System.out.println("File Name = " + item.getFieldName()
							+ ", Value = " + item.getString());
				} else {
					System.out.println("Field Name = " + item.getFieldName()
							+ ", File Name = " + item.getName()
							+ ", Content type = " + item.getContentType()
							+ ", File Size = " + item.getSize());

					File file = new File(ProjectConstants.xmlPath,
							item.getName());
					// System.out.println(file.getAbsolutePath());
					item.write(file);

					XMLToDB xmltodb = new XMLToDB(file.getAbsolutePath());
					xmltodb.run();

				}
				System.out.close();
			}
		} catch (FileUploadException ex) {
			System.out.println("Error encountered while parsing the request"
					+ ex);
		} catch (Exception ex) {
			System.out.println("Error encountered while uploading file" + ex);
		}
		// Sale() proc needs to be called after every xml upload.
		Connection conn = this.getconnection();
		try {
			System.out.println("Sale called");
			String simpleProc = "{ call sale()}";
				CallableStatement cs = conn.prepareCall(simpleProc);
				cs.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	
	}

	public String getForecastData(String eventName) {
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		ForecastBean forecastBean = null;
		JSONObject json = new JSONObject();
		JSONArray forecasts = new JSONArray();

		try {
			pstmt = conn
					.prepareStatement("SELECT 1 AS No,Ticket,Amount,Total_Seats,A,B,C,D,E,F,G,H from forecast_game_capacity f ,event e where f.Event_Id=e.Event_Id and e.Event_Code = "+"'"+eventName+"'"
							+ " UNION "
							+ "SELECT 2 AS No,Ticket,Amount,Total_Seats,A,B,C,D,E,F,G,H from forecast_game_kills f ,event e where f.Event_Id=e.Event_Id and e.Event_Code = "+"'"+eventName+"'"
							+ " UNION "
							+ "SELECT 3 AS No, Ticket,Amount,Total_Seats,A,B,C,D,E,F,G,H from forecast_game_net_capacity f ,event e where f.Event_Id=e.Event_Id and e.Event_Code = "+"'"+eventName+"'"
							+ " UNION "
							+ "SELECT 4 AS No, Ticket,Amount,Total_Seats,A,B,C,D,E,F,G,H from forecast_game_sold f ,event e where f.Event_Id=e.Event_Id and e.Event_Code  = "+"'"+eventName+"'"
							+ " UNION "
							+ "SELECT 5 AS No, Ticket,Amount,Total_Seats,A,B,C,D,E,F,G,H from forecast_game_edit_table f ,event e where f.Event_Id=e.Event_Id and e.Event_Code  = "+"'"+eventName+"'"
							+ " UNION "
							+ "SELECT 6 AS No, 'PROJ-ACT' as Ticket,NULL as Amount,NULL as Total_Seats, NULL as A, Null as B, NULL as C, NULL as D, NULL as E, NULL as F, NULL as G,NULL as H "
							+ " UNION ALL "
							+ "SELECT 7 AS No, Ticket,Amount,Total_Seats,A,B,C,D,E,F,G,H from forecast_game_remaining_table f ,event e where f.Event_Id=e.Event_Id and e.Event_Code  = "+"'"+eventName+"'"
							+ " UNION ALL "
							+ " SELECT 8 As No, Ticket,Amount,Total_Seats,A,B,C,D,E,F,G,H from forecast_game_total f ,event e where f.Event_Id=e.Event_Id and e.Event_Code  = "+"'"+eventName+"' ORDER BY No,Ticket");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				forecastBean = new ForecastBean();
				forecastBean.setNo(rs.getInt(1));
				forecastBean.setTicket(rs.getString(2));
				forecastBean.setAmount(rs.getInt(3));
				forecastBean.setTotal_Seats(rs.getInt(4));
				forecastBean.setA(rs.getInt(5));
				forecastBean.setB(rs.getInt(6));
				forecastBean.setC(rs.getInt(7));
				forecastBean.setD(rs.getInt(8));
				forecastBean.setE(rs.getInt(9));
				forecastBean.setF(rs.getInt(10));
				forecastBean.setG(rs.getInt(11));
				forecastBean.setH(rs.getInt(12));
				forecasts.put(forecastBean.toJSONObject());
			}
			json.put("items", forecasts);
			rs.close();
		} catch (SQLException | JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return json.toString();
	}

	public String getSeasonForecastData() {
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		SeasonForecastBean seasonforecastBean = null;
		JSONObject json = new JSONObject();
		JSONArray seasonforecasts = new JSONArray();

		try {
			pstmt = conn.prepareStatement("SELECT * FROM season_forecast");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				seasonforecastBean = new SeasonForecastBean();
				seasonforecastBean.setSeason(rs.getString(1));
				seasonforecastBean.setOpponents_Name(rs.getString(2));
				seasonforecastBean.setEvent_Date(rs.getString(3));
				seasonforecastBean.setDay(rs.getString(4));
				seasonforecastBean.setCapacity(rs.getInt(5));
				seasonforecastBean.setKilss(rs.getInt(6));
				seasonforecastBean.setNet_Capacity(rs.getInt(7));
				seasonforecastBean.setFulls(rs.getInt(8));
				seasonforecastBean.setFulls_Revenue(rs.getInt(9));
				seasonforecastBean.setPartials(rs.getInt(10));
				seasonforecastBean.setPartials_Revenue(rs.getInt(11));
				seasonforecastBean.setGroups(rs.getInt(12));
				seasonforecastBean.setGroupsRevenue(rs.getInt(13));
				seasonforecastBean.setIndis(rs.getInt(14));
				seasonforecastBean.setIndis_Revenue(rs.getInt(15));
				seasonforecastBean.setTotal_Ticket(rs.getInt(16));
				seasonforecastBean.setComps(rs.getInt(17));
				seasonforecastBean.setUnsold(rs.getInt(18));
				seasonforecastBean.setGross_Revenue(rs.getInt(19));
				seasonforecastBean.setRevenue(rs.getInt(20));
				seasonforecastBean.setBudget(rs.getInt(21));
				seasonforecastBean.setBudget_Tickets(rs.getInt(22));
				seasonforecastBean.setBudget_Variance(rs.getInt(23));
				seasonforecastBean.setForecast_Revenue(rs.getInt(24));
				seasonforecastBean.setForecast_Tickets(rs.getInt(25));
				seasonforecasts.put(seasonforecastBean.toJSONObject());
			}
			json.put("items", seasonforecasts);
			rs.close();
		} catch (SQLException | JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return json.toString();
	}

	public String getSeasonData(String season)

	{
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		SeasonBean S1Bean = null;
		JSONObject json = new JSONObject();
		JSONArray S1 = new JSONArray();

		try {
			
			pstmt = conn
					.prepareStatement("SELECT ticket_level_name,ticket_group_name,sold_seats,total_amount,percentage_capacity,season FROM season_stat WHERE season like "+season);
			// System.out.println(pstmt);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				S1Bean = new SeasonBean();
				S1Bean.setTicket_Level(rs.getString(1));
				S1Bean.setTicket_Group(rs.getString(2));
				S1Bean.setSold_Seats(rs.getInt(3));
				S1Bean.setTotal_Amount(rs.getDouble(4));
				S1Bean.setPercentage_Capacity(rs.getDouble(5));
				S1Bean.setSeason(rs.getString(6));
				S1.put(S1Bean.toJSONObject());
			}
			json.put("items", S1);
			rs.close();
		} catch (SQLException | JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return json.toString();
	}
	public String getSeasons()

	{
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		SeasonBean S1Bean = null;
		JSONObject json = new JSONObject();
		JSONArray S1 = new JSONArray();

		try {
			
			pstmt = conn
					.prepareStatement("SELECT distinct season from season_stat ");
			// System.out.println(pstmt);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				S1Bean = new SeasonBean();
				S1Bean.setSeason(rs.getString(1));
				S1.put(S1Bean.toJSONObject());
			}
			json.put("items", S1);
			rs.close();
		} catch (SQLException | JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return json.toString();
	}

	
	public String getPriceData() {
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		PriceBean priceBean = null;
		JSONObject json = new JSONObject();
		JSONArray prices = new JSONArray();

		try {
			pstmt = conn
					.prepareStatement("select ticket_level_name, ticket_group_name,price from price_group_prediction a join ticket_group_prediction b on a.ticket_group_prediction_id=b.ticket_group_prediction_id"
							+ " join ticket_level c on c.ticket_level_id=a.ticket_level_id");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				priceBean = new PriceBean();
				priceBean.setTicket_Level(rs.getString(1));
				priceBean.setTicket_Group(rs.getString(2));
				priceBean.setPrice(rs.getInt(3));

				prices.put(priceBean.toJSONObject());
			}
			json.put("items", prices);
			rs.close();
		} catch (SQLException | JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return json.toString();
	}

	public String getTicketGroupData() {
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		TicketGroupBean ticketgroupBean = null;
		JSONObject json = new JSONObject();
		JSONArray ticketgroups = new JSONArray();

		try {
			pstmt = conn
					.prepareStatement("SELECT Ticket_Type_Code,Price_Level_Code,Ticket_Group_Name FROM `ticket_group` a join ticket_type b "
							+ "on a.ticket_type_id=b.ticket_type_id join price_level_index c "
							+ "on a.price_level_id=c.price_level_id join ticket_group_prediction d "
							+ "on a.ticket_group_prediction_id=d.ticket_group_prediction_id;");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				ticketgroupBean = new TicketGroupBean();
				ticketgroupBean.setTicketTypeCode(rs.getString(1));
				ticketgroupBean.setPriceLevelCode(rs.getString(2));
				ticketgroupBean.setTicketGroupName(rs.getString(3));
				ticketgroups.put(ticketgroupBean.toJSONObject());
			}
			json.put("items", ticketgroups);
			rs.close();
		} catch (SQLException | JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return json.toString();
	}
	
	public String getGraphData() {
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		GraphBean graphBean = null;
		JSONObject json = new JSONObject();
		JSONArray tickets = new JSONArray();

		try {

			pstmt = conn
					.prepareStatement("select * from graph_stat");

			rs = pstmt.executeQuery();
			while (rs.next()) {

				graphBean = new GraphBean();
				graphBean.setEvent_Code(rs.getString(1));
				graphBean.setTicket_Level_Name(rs.getString(2));
				graphBean.setSold_Seats(rs.getInt(3));
				graphBean.setComp_Seats(rs.getInt(4));
				graphBean.setTotal_Amount(rs.getInt(5));
				graphBean.setCapacity(rs.getInt(6));

				tickets.put(graphBean.toJSONObject());
			}
			json.put("items", tickets);
			rs.close();
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}
		catch(JSONException ex) {
			ex.printStackTrace();
		}
		finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return json.toString();
	}
	
	public String getForecastGraph(String eventName)
	{
		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs ;
		ForecastBean forecastBean = null;
		JSONObject json = new JSONObject();
		JSONArray  forecasts = new JSONArray();

		try {
			pstmt = conn.prepareStatement(
					"SELECT 4 As No, Ticket,Amount,Total_Seats,A,B,C,D,E,F,G,H from forecast_game_sold f ,event e where f.Event_Id=e.Event_Id and e.Event_Code  = "+"'"+eventName+"'"
							+ " UNION "
							+ "SELECT 5 As No, Ticket,Amount,Total_Seats,A,B,C,D,E,F,G,H from forecast_game_edit_table f ,event e where f.Event_Id=e.Event_Id and e.Event_Code  = "+"'"+eventName+"'");
			rs = pstmt.executeQuery();
			while(rs.next()){

				forecastBean = new ForecastBean();
        forecastBean.setNo(rs.getInt(1));
        forecastBean.setTicket(rs.getString(2));
        forecastBean.setAmount(rs.getInt(3));
        forecastBean.setTotal_Seats(rs.getInt(4));
        forecastBean.setA(rs.getInt(5));
        forecastBean.setB(rs.getInt(6));
        forecastBean.setC(rs.getInt(7));
        forecastBean.setD(rs.getInt(8));
        forecastBean.setE(rs.getInt(9));
        forecastBean.setF(rs.getInt(10));
        forecastBean.setG(rs.getInt(11));
        forecastBean.setH(rs.getInt(12));
        forecasts.put(forecastBean.toJSONObject());
			}
			json.put("items", forecasts);
			rs.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		catch(JSONException e) {
			e.printStackTrace();
		}
		finally {
			if(conn !=null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return json.toString();
	}
	
	
	public void saveSeasonForecast(ArrayList<SeasonForecastBean> sfList) {
		Connection conn = this.getconnection();
		try {
			String simpleProc = "{ call budget(?,?,?,?) }";
			for (SeasonForecastBean sf : sfList) {
				CallableStatement cs = conn.prepareCall(simpleProc);
				cs.setInt(1, Integer.parseInt(sf.getSeason()));
				cs.setString(2, sf.getEvent_Date());
				cs.setInt(3, sf.getBudget());
				cs.setInt(4, sf.getBudget_Tickets());
				cs.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}
	
	public void saveForecast(ArrayList<ForecastBean> fList , String event) {
		Connection conn = this.getconnection();
		try {
			String simpleProc = "{ call forecast(?,?,?,?,?,?,?,?,?,?,?,?)}";
			int event_id =  this.getEventId(event);
			
			for (ForecastBean f : fList) {
				CallableStatement cs = conn.prepareCall(simpleProc);
				cs.setInt(1,event_id);
				cs.setString(2, f.getTicket());
				cs.setInt(3, f.getAmount());
				cs.setInt(4, f.getTotal_Seats());
				cs.setInt(5, f.getA());
				cs.setInt(6, f.getB());
				cs.setInt(7, f.getC());
				cs.setInt(8, f.getD());
				cs.setInt(9, f.getE());
				cs.setInt(10, f.getF());
				cs.setInt(11, f.getG());
				cs.setInt(12,f.getH());
				cs.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}
	
	public void savePrice(ArrayList<PriceBean> pList ) {
		Connection conn = this.getconnection();
		try {
			String simpleProc = "{ call prices(?,?,?)}";
			
			for (PriceBean f : pList) {
				CallableStatement cs = conn.prepareCall(simpleProc);
				cs.setString(1, f.getTicket_Group());
				cs.setString(2, f.getTicket_Level());
				cs.setInt(3, f.getPrice());
				cs.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}
	
	public void saveTicketGroup(ArrayList<TicketGroupBean> tgList) {
		Connection conn = this.getconnection();
		try {
			String simpleProc = "{ call ticket_group(?,?,?)}";
			
			for (TicketGroupBean f : tgList) {
				CallableStatement cs = conn.prepareCall(simpleProc);
				cs.setString(1, f.getTicketTypeCode());
				cs.setString(2, f.getPriceLevelCode());
				cs.setString(3,f.getTicketGroupName() );
				cs.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
	}
	
	public int getEventId(String Event) {

		Connection conn = this.getconnection();
		PreparedStatement pstmt;
		ResultSet rs;
		int event_id = 0;
		try {
			pstmt = conn.prepareStatement("select Event_Id from event where Event_Code = "+ "'"+Event+"'");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				event_id = rs.getInt(1);
				System.out.println("event_id"+event_id);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return event_id;
	}

	

	
}
